﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsWorkerDAL.cs
/// 作  者：Kidy
/// 时  间：2006.11.05
/// 说  明：ClsDepClsWorkerDALtDAL类用于员工信息相关处理
/// </summary>
public class ClsWorkerDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 获取所有的员工信息
    /// </summary>
    /// <returns>返回员工信息数据集</returns>
    public DataSet GetWorkers()
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetDataWorker", con);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 用于查询窗口获取数据
    /// </summary>
    /// <returns></returns>
    public DataSet SearchWorkers()
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_QueryWinGetDataByWorker", con);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 搜索员工信息
    /// </summary>
    /// <param name="Key">搜索关键字</param>
    /// <returns>返回搜索信息数据集</returns>
    public DataSet SearchWorkers(string Key)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_SearchWorkers", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@key", Key);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 检验员工编号是否存在
    /// </summary>
    /// <param name="Wk_ID">员工编号</param>
    /// <returns>返回－1检验失败，0编号不存在，大于0存在</returns>
    public int CheckUpWorkerID(string Wk_ID)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_CheckUpWorkerID", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@wk_id", SqlDbType.NVarChar, 20);
        com.Parameters["@wk_id"].Value = Wk_ID;

        try
        {
            con.Open();

            return (int)com.ExecuteScalar();
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>   
    /// 添加员工信息
    /// </summary>
    /// <param name="Wk_ID">员工编号</param>
    /// <param name="Wk_Oper">录入人</param>
    /// <returns>返回添加是否成功</returns>
    /// strWk_ID, strWk_Name, decWk_Rate, strWk_Mode, strWk_Remark, "思捷用户"
    /// strWk_ID, strWk_Name, strWk_Sex, strWk_Birth,strWk_Remark,Oper
     //Boolean bolIsCussee = cls_Worker.AddWorker( txtAddSid,
     //          dropZz,dropMarry,dropNation,txtAddDept,txtAddInDate,dropDegree,dropJz,txtAddDuty,txtWordClass,
     //        dropWkType,txtAddress,txtAddPost,txtAddTel,txtAddMobile,txtAddEmail,txtAddWebSite,txtAddSchool,
      //       txtAddMajor,CheckBox11,"思捷用户",txtAddRemark);

    public Boolean AddWorker(string sWk_ID, string sWk_Name, string sWk_Sex, DateTime sWk_Birth,
        string txtAddSid,string dropZz,string dropMarry,string dropNation,string txtAddDept,string txtAddInDate,
        string dropDegree, string dropJz, string txtAddDuty, string txtWordClass, string m, string n,
        string  dropWkType,string txtAddress,string txtAddPost,string txtAddTel,
        string txtAddMobile,string txtAddEmail,string txtAddWebSite,string txtAddSchool,
             string txtAddMajor,bool CheckBox11,string sWk_Oper,bool t,string txtAddRemark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddWorker", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Wk_ID", SqlDbType.NVarChar, 20);
        com.Parameters["@Wk_ID"].Value = sWk_ID;
        com.Parameters.Add("@wk_sid", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_sid"].Value = txtAddSid;
        com.Parameters.Add("@wk_zz", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_zz"].Value = dropZz;
        com.Parameters.Add("@wk_marry", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_marry"].Value = dropMarry;
        com.Parameters.Add("@wk_nation", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_nation"].Value = dropNation;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 60);
        com.Parameters["@dp_id"].Value = txtAddDept;
        com.Parameters.Add("@in_date", SqlDbType.NVarChar, 60);
        com.Parameters["@in_date"].Value = txtAddInDate;
        com.Parameters.Add("@wk_degree", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_degree"].Value = dropDegree;
        com.Parameters.Add("@wk_jz", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_jz"].Value = dropJz;
        com.Parameters.Add("@bk_id", SqlDbType.NVarChar, 60);
        com.Parameters["@bk_id"].Value = txtAddDuty;     ///
        com.Parameters.Add("@bk_no", SqlDbType.NVarChar, 60);
        com.Parameters["@bk_no"].Value = txtWordClass;  ///
        com.Parameters.Add("@wk_type", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_type"].Value = dropWkType;

        com.Parameters.Add("@wk_address", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_address"].Value = txtAddress;
        com.Parameters.Add("@wk_post", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_post"].Value = txtAddPost;
        com.Parameters.Add("@wk_tel", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_tel"].Value = txtAddTel;
        com.Parameters.Add("@wk_mobile", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_mobile"].Value = txtAddMobile;
        com.Parameters.Add("@wk_email", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_email"].Value = txtAddEmail;
        com.Parameters.Add("@wk_website", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_website"].Value = txtAddWebSite;
        com.Parameters.Add("@wk_school", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_school"].Value = txtAddSchool;
        com.Parameters.Add("@wk_major", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_major"].Value = txtAddMajor;
        com.Parameters.Add("@is_flag", SqlDbType.Bit );
        com.Parameters["@is_flag"].Value = CheckBox11;///
        //com.Parameters.Add("@Oper", SqlDbType.NVarChar, 60);
        //com.Parameters["@Oper"].Value = sWk_Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 60);
        com.Parameters["@remark"].Value = txtAddRemark;
                     
        com.Parameters.Add("@Wk_Name", SqlDbType.NVarChar, 60);
        com.Parameters["@Wk_Name"].Value = sWk_Name;
        com.Parameters.Add("@Wk_Sex", SqlDbType.NVarChar,2);
        com.Parameters["@Wk_Sex"].Value = sWk_Sex;
        com.Parameters.Add("@Wk_Birth", SqlDbType.DateTime,8);
        com.Parameters["@Wk_Birth"].Value = sWk_Birth;
        com.Parameters.Add("@Oper", SqlDbType.NVarChar, 12);
        com.Parameters["@Oper"].Value = sWk_Oper;
        com.Parameters.Add("@dt_id", SqlDbType.NVarChar, 60);
        com.Parameters["@dt_id"].Value = m;
        com.Parameters.Add("@wc_id", SqlDbType.NVarChar, 60);
        com.Parameters["@wc_id"].Value = n;
        com.Parameters.Add("@is_stop", SqlDbType.Bit);
        com.Parameters["@is_stop"].Value = t;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 修改员工信息
    /// </summary>
    /// <param name="Wk_ID">员工编号</param>
    /// <param name="Field_Name">要修改的字段</param>
    /// <param name="Field_Value">字段值</param>
    /// <returns>返回修改是否成功</returns>
    public Boolean UpDateWorker(string sWk_ID, string sWk_name, string sWk_sex, string sWk_birthday, string txtAddSid,
        string dropZz, string dropMarry, string dropNation, string txtAddDept, string txtAddInDate, string dropDegree,
        string dropJz, string txtAddDuty, string txtWordClass, string dropWkType,
        string txtAddress, string txtAddPost, string txtAddTel,
        string txtAddMobile,string txtAddEmail,string txtAddWebSite,string txtAddSchool,
             string txtAddMajor,string txtRemark,bool t)
    {
        //wk_id.Text.Trim(), wk_name.Text.Trim(), wk_sex.SelectedItem.Value, wk_birthday.Text.Trim()
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        //SqlCommand com = new SqlCommand("Update tbiWorker set " + Field_Name + "='" + Field_Value + "' Where wk_id='" + Wk_ID + "'", con);
        /**/
        SqlCommand com = new SqlCommand("Pro_UpDateWorker", con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Wk_ID", SqlDbType.NVarChar, 20);
        com.Parameters["@Wk_ID"].Value = sWk_ID;
        com.Parameters.Add("@Wk_name", SqlDbType.NVarChar, 12);
        com.Parameters["@Wk_name"].Value = sWk_name;
        com.Parameters.Add("@Wk_sex", SqlDbType.NVarChar,2);
        com.Parameters["@Wk_sex"].Value = sWk_sex;
        com.Parameters.Add("@Wk_birthday", SqlDbType.DateTime,8);
        com.Parameters["@Wk_birthday"].Value = DateTime.Parse(sWk_birthday);

        com.Parameters.Add("@wk_sid", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_sid"].Value = txtAddSid;
        com.Parameters.Add("@wk_zz", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_zz"].Value = dropZz;
        com.Parameters.Add("@wk_marry", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_marry"].Value = dropMarry;
        com.Parameters.Add("@wk_nation", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_nation"].Value = dropNation;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 60);
        com.Parameters["@dp_id"].Value = txtAddDept;
        com.Parameters.Add("@in_date", SqlDbType.DateTime, 8);
        com.Parameters["@in_date"].Value = DateTime.Parse(txtAddInDate);
        com.Parameters.Add("@wk_degree", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_degree"].Value = dropDegree;
        com.Parameters.Add("@wk_jz", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_jz"].Value = dropJz;
        com.Parameters.Add("@bk_id", SqlDbType.NVarChar, 60);
        com.Parameters["@bk_id"].Value = txtWordClass;     ///
        com.Parameters.Add("@bk_no", SqlDbType.NVarChar, 60);
        com.Parameters["@bk_no"].Value = "未知";  ///////------待调试！！！
        com.Parameters.Add("@wk_type", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_type"].Value = txtAddDuty ;////------待调试！！！

        com.Parameters.Add("@dt_id", SqlDbType.NVarChar, 60);
        com.Parameters["@dt_id"].Value = dropWkType;//////////////
        com.Parameters.Add("@wc_id", SqlDbType.NVarChar, 60);
        com.Parameters["@wc_id"].Value = txtWordClass;//////////

       
        com.Parameters.Add("@wk_address", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_address"].Value = txtAddress;
        com.Parameters.Add("@wk_post", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_post"].Value = txtAddPost;
        com.Parameters.Add("@wk_tel", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_tel"].Value = txtAddTel;
        com.Parameters.Add("@wk_mobile", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_mobile"].Value = txtAddMobile;
        com.Parameters.Add("@wk_email", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_email"].Value = txtAddEmail;
        com.Parameters.Add("@wk_website", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_website"].Value = txtAddWebSite;
        com.Parameters.Add("@wk_school", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_school"].Value = txtAddSchool;
        com.Parameters.Add("@wk_major", SqlDbType.NVarChar, 60);
        com.Parameters["@wk_major"].Value = txtAddMajor;

        com.Parameters.Add("@remark", SqlDbType.NVarChar, 60);
        com.Parameters["@remark"].Value = txtRemark;
        com.Parameters.Add("@is_flag", SqlDbType.Bit);
        com.Parameters["@is_flag"].Value = t;


        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 作废员工信息
    /// </summary>
    /// <param name="Wk_ID">员工编号</param>
    /// <returns>返回作废是否成功</returns>
    public Boolean DeleteWorker(string Wk_ID, int Is_Visible)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteWorker", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Wk_ID", SqlDbType.NVarChar, 20);
        com.Parameters["@Wk_ID"].Value = Wk_ID;
        com.Parameters.Add("@is_visible", SqlDbType.Int, 1);
        com.Parameters["@is_visible"].Value = Is_Visible;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 确认员工信息
    /// </summary>
    /// <param name="Wk_ID">员工编号</param>
    /// <param name="AuditStat">确认状态</param>
    /// <param name="Auditor">确认人</param>
    /// <returns>返回确认是否成功</returns>
    public Boolean AuditStatWorker(string Wk_ID, int AuditStat, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditStatSetWorker", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@Wk_ID", SqlDbType.NVarChar, 20);
        com.Parameters["@Wk_ID"].Value = Wk_ID;
        com.Parameters.Add("@is_audit", SqlDbType.Int);
        com.Parameters["@is_audit"].Value = AuditStat;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
}
